﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.CompilerServices;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Data.SqlClient;
using DreamBuilder.Core.DAO;
using DreamBuilder.Core;
using DreamBuilder.Common;

namespace Web.SystemLogin.DataAccess
{
    public class DALSystemLogin : DataAccessBase
    {
        public DALSystemLogin()
        {
            if (this.DBHelper == null)
            {
                this.DBHelper = DBHelperFactory.CreateInstance(DbHelperName.Business);
            }
        }
        internal DataTable CheckLoginNameAvailable(string loginName)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] { new OracleParameter(":Login_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE Login_ID=:Login_ID";
            }
            else
            {
                arrParam = new DbParameter[] { new SqlParameter("@Login_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE Login_ID=@Login_ID";
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }
        //by lyn oa用户登录 
        internal DataTable CheckOALoginNameAvailable(string loginName)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] { new OracleParameter(":OALogin_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE OALogin_ID=:OALogin_ID";
            }
            else
            {
                arrParam = new DbParameter[] { new SqlParameter("@OALogin_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE OALogin_ID=@OALogin_ID";
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }
        //by lyn cnpc用户登录 
        internal DataTable CheckCNPCLoginNameAvailable(string loginName)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] { new OracleParameter(":Login_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE Login_ID=:Login_ID and Can_Login=1 ";
            }
            else
            {
                arrParam = new DbParameter[] { new SqlParameter("@Login_ID", loginName) };
                strSql = "SELECT User_ID FROM  P_Auth_User WHERE Login_ID=@Login_ID and Can_Login=1 ";
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }

        internal DataTable CheckUserPassword(string userID, string passwd)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] {
                    new OracleParameter(":User_ID", userID) ,
                    new OracleParameter(":User_Pwd", passwd) 
                };
                strSql = "select * from P_Auth_User where User_ID =:User_ID and User_Pwd=:User_Pwd and Can_Login=1";
            }
            else
            {
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID) ,
                    new SqlParameter("@User_Pwd", passwd) 
                };
                // modify by lyn strSql = "select * from P_Auth_User where User_ID =@User_ID and User_Pwd=@User_Pwd and Can_Login=1";
                strSql = "select * from P_Auth_User where User_ID =@User_ID and User_Pwd=@User_Pwd and Can_Login=1 and status_id='User_Status--1'";
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }

        internal DataTable GetCurrentUser(string userID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] {
                    new OracleParameter(":User_ID", userID) 
                };
                strSql = "SELECT * FROM P_Auth_User where User_ID=:User_ID ";
            }
            else
            {
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID)
                };
                strSql = "SELECT * FROM P_Auth_User where User_ID=@User_ID "; 
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }

        internal void UpdateUserLoginInfo(string userID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] {
                    new OracleParameter(":User_ID", userID) 
                };
                strSql = "update P_Auth_User set Last_Login_Time = This_Login_Time, This_Login_Time = sysdate where User_ID=:User_ID ";
            }
            else
            {
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID)
                };
                strSql = "update P_Auth_User set Last_Login_Time = This_Login_Time, This_Login_Time = GETDATE() where User_ID=@User_ID ";
            }
            DBHelper.ExecuteSql(strSql, arrParam);
        }

        internal DataTable GetAllOrgID(string userID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] {
                    new OracleParameter(":User_ID", userID) 
                };
                strSql = "select ORG_ID from P_Auth_ORG_User where User_ID=:User_ID ";
            }
            else
            {
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID)
                };
                strSql = "select ORG_ID from P_Auth_ORG_User where User_ID=@User_ID ";
            }
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }

        internal bool UpdateUserPassword(string loginname, string password)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                //时间：2013年3月26日 00:52:31
                //备注：更新密码由 OALOGIN_ID 筛选改为由 LOGIN_ID筛选
                //彭杰杰
                arrParam = new DbParameter[] {
                    new OracleParameter(":login_Id", loginname),
                    new OracleParameter(":User_Pwd", password)
                };
                strSql = "Update  P_auth_User set User_Pwd=:User_Pwd where login_Id=:login_Id ";
                return DBHelper.ExecuteSql(strSql, arrParam) > 0 ? true : false;
            }
            else
            {
                throw new Exception("目前只支持oracle数据库");
            }
        }

       #region 更新CNPC用户名
        internal void UpdateUserCNPCLoginName(string cnpcUserID,string loginName)
        {
            string strSql = "Update P_Auth_User set cnpcVerification=1, Login_ID='" + loginName + "' where User_ID='" + cnpcUserID + "'";
            DBHelper.ExecuteSql(strSql);
        }
       #endregion
        #region
        internal DataTable GetCNPCVerification(string userID)
        {
            string strSql = "select cnpcVerification from P_Auth_User where User_ID = '" + userID + "'";
            return this.DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
        #endregion
        #region add by lyn 20111027 lyn cnpc can login

        internal DataTable CheckCnpcLoginInfo(string userID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID) 
                };
                strSql = "select * from P_Auth_User where User_ID =@User_ID and Can_Login=1 and status_id='User_Status--1'";           
            return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }
      #endregion
           #region add by lyn 20111101 lyn get all orgname
        internal DataTable GetUserAllOrgNameInfo(string userID)
        {
            DbParameter[] arrParam;
            string strSql = string.Empty;
            if (DBHelper.database is Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase)
            {
                arrParam = new DbParameter[] {
                    new OracleParameter(":User_ID", userID) 
                };
                strSql = "select distinct o.* from P_AUTH_ORG  o,P_AUTH_USER u where u.org_id = o.ORG_ID and u.user_id=:User_ID connect by prior o.ORG_ID=o.Parent_ID";
            }
            else
            {
                arrParam = new DbParameter[] {
                    new SqlParameter("@User_ID", userID) 
                };
                strSql = "with c as	( select d.org_id,Parent_ID,org_Name from P_Auth_ORG d ,p_auth_user u where u.user_id=@User_ID and u.org_id=d.org_id union all select a.org_id,a.Parent_ID,a.org_name from P_Auth_ORG  as a	 join c on c.Parent_ID = a.org_id ) select ORG_ID,Parent_ID,ORG_Name from c where parent_ID is not null; ";           
            }
                return DBHelper.ExecuteDataSet(strSql, arrParam).Tables[0];
        }
        internal DataTable GetUserOrgNameInfo(string userID)
        {
            string strSql = "select ORG_Name from p_auth_user a,p_auth_org b where a.org_id=b.org_id and a.user_id='"+userID+"' ";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }

        internal DataTable GetUserOrgFullFondsInfo(string userID)
        {
            string strSql = "select org_desc from p_auth_user a,p_auth_org b where a.org_id=b.org_id and a.user_id='" + userID + "' ";
            return DBHelper.ExecuteDataSet(strSql).Tables[0];
        }
      #endregion
        #region add by guoyin
        public string GetUserIdByLoginId(string loginId)
        {
            string UserId = null;
            string sql = "select User_ID from P_Auth_User where Login_ID='" + loginId + "'";
            IDataReader dr = this.DBHelper.ExecuteReader(sql);
            if (dr != null)
            {
                if (dr.Read())
                {
                    UserId = dr[0].ToString();
                }
            }
            return UserId;
        }
        #endregion
    }
}
